﻿CREATE PROCEDURE [dbo].[User_CheckHashCode]
	@email nvarchar(256)
	,@hashCode nvarchar(32)
	,@spUpdates NVARCHAR(MAX) = NULL
	,@spDelimiter NVARCHAR(4)
AS
BEGIN
	SET @email = LOWER(@email)

	DECLARE @userId UNIQUEIDENTIFIER, @isIdentify BIT

	SELECT @userId = [UserId], @isIdentify = [IsIdentify] FROM [dbo].[user_User] WHERE [Email] = @email AND [HashCode] = @hashCode

	IF @userId IS NOT NULL
	BEGIN
		SELECT @userId AS 'UserId', @hashCode AS 'HashCode', @isIdentify AS 'IsIdentify'

		DECLARE @roles TABLE
		(
			[Role] NVARCHAR(128)
		)

		INSERT @roles
		SELECT [Role]
		FROM [dbo].[user_Role]
		WHERE Email = @email

		IF EXISTS (SELECT * FROM [dbo].[text_User] WHERE [UserId] = @userId AND [Expired] > GETUTCDATE())
		BEGIN
			INSERT @roles
			VALUES ('Viewer')
		END
		ELSE IF EXISTS (SELECT * FROM [dbo].[purchase_Item] WHERE [UserId] = @userId AND [PaymentProcessStatus] = 'success' AND DATEADD(mm, 1, [PaymentProcessDate]) > GETUTCDATE()) 
		BEGIN
			INSERT @roles
			VALUES ('Viewer')
		END

		SELECT [Role]
		FROM @roles

		SELECT CAST(CASE WHEN EXISTS	(
																			SELECT *
																			FROM [dbo].[email_Subscriber] AS es
																			INNER JOIN [dbo].[user_User] AS u ON u.Email = es.Email
																			WHERE u.UserId = @userId
																		)
								THEN 1
								ELSE 0
								END
					 AS BIT) AS [IsSubscriber]


		SELECT [Count]
		FROM [dbo].[user_Preview]
		WHERE [UserId] = @userId

		IF @spUpdates IS NOT NULL
		BEGIN
			DECLARE @CURSOR CURSOR
			DECLARE @SPSTRING NVARCHAR(MAX)

			SET @CURSOR  = CURSOR SCROLL
			FOR
			SELECT [value] FROM [dbo].[SplitStringByDelimiter] (@spUpdates, @spDelimiter)
			/*Открываем курсор*/
			OPEN @CURSOR
			/*Выбираем первую строку*/
			FETCH NEXT FROM @CURSOR INTO @SPSTRING
			/*Выполняем в цикле перебор строк*/
			WHILE @@FETCH_STATUS = 0
			BEGIN
				BEGIN TRY
					DECLARE @spstr NVARCHAR(MAX) = @SPSTRING + ', @userId = ''' + CONVERT(NVARCHAR(50), @userId) + ''''
					EXEC sp_executesql @spstr
				END TRY
				BEGIN CATCH
					DECLARE @ErrorMessage NVARCHAR(4000);
					DECLARE @ErrorSeverity INT;
					DECLARE @ErrorState INT;

					SELECT	@ErrorMessage = ERROR_MESSAGE(),
									@ErrorSeverity = ERROR_SEVERITY(),
									@ErrorState = ERROR_STATE();

					SELECT @ErrorMessage, @ErrorSeverity, @ErrorState
				END CATCH

				/*Выбираем следующую строку*/
				FETCH NEXT FROM @CURSOR INTO @SPSTRING
			END
			CLOSE @CURSOR	
		END
	END
END